﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using InterpriseSuiteEcommerceCommon.DTO;
using InterpriseSuiteEcommerceCommon.Extensions;
using System.Text;
using Interprise.Framework.Customer.DatasetComponent;

namespace InterpriseSuiteEcommerceCommon.DataAccess
{
    public class CustomerDA
    {
        /// <summary>
        /// Retrieve all payment term group of specific customer's contact
        /// </summary>
        /// <param name="contactCode">string Contact Code</param>
        /// <returns> IEnumerable(PaymentTermDTO)</returns>
        public static IEnumerable<PaymentTermDTO> GetAllForGroup(string contactCode, Address preferredShippingAddress)
        {
            var terms = new PaymentTermDTO.PaymentTermGroup();
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, "exec eCommerceGetPaymentTermGroup @ContactCode = {0}, @IsAnonymous = {1}, @CountryCode = {2}, @BusinessType = {3}",
                                                        DB.SQuote(contactCode),
                                                        Convert.ToInt32(Customer.Current.IsNotRegistered),
                                                        DB.SQuote(preferredShippingAddress.Country),
                                                        DB.SQuote(Customer.Current.DefaultPrice)))
                {
                    while (reader.Read())
                    {
                        if (DB.RSField(reader, "PaymentTermCode") != AppLogic.AppConfig("PaymentTermCodeZeroDollarOrder"))
                        {
                            var term = new PaymentTermDTO(DB.RSField(reader, "PaymentTermCode"),
                                                    DB.RSField(reader, "PaymentMethodCode"),
                                                    DB.RSField(reader, "PaymentTermDescription"),
                                                    true);

                            term.IsSelected = DB.RSFieldBool(reader, "IsDefault");
                            terms.AddTerm(term);
                        }
                    }
                }
                con.Close();
            }
            terms.EnsureHasSelected();
            return terms;
        }

        /// <summary>
        /// Validate if address has changes from the DB vs inputted address
        /// </summary>
        /// <param name="creditCardRow">CreditCardDataset.CustomerCreditCardViewRow object</param>
        /// <param name="billToAddress">Address object</param>
        /// <returns>boolean</returns>
        public static bool HasChangesToAddressInfo(CreditCardDataset.CustomerCreditCardViewRow creditCardRow, Address billToAddress)
        {
            bool hasChanges = false;
            try
            {
                hasChanges = !(creditCardRow.NameOnCard == billToAddress.CardName &&
                        creditCardRow.PostalCode == billToAddress.PostalCode &&
                        creditCardRow.City == billToAddress.City &&
                        creditCardRow.Country == billToAddress.Country &&
                        creditCardRow.State == billToAddress.State);
            }
            catch (InvalidCastException)
            {

                //Added some exception handling for IS Address validation
                //creation of Address in IS might omit filling-up fields that might required in Ecommerce.
                //So, when exception occurs, address has been changed in Ecommerce.

                hasChanges = true;
            }
            catch (StrongTypingException)
            {

                //Added some exception handling for IS Address validation
                //creation of Address in IS might omit filling-up fields that might required in Ecommerce.
                //So, when exception occurs, address has been changed in Ecommerce.

                hasChanges = true;
            }
            catch
            {
                throw;
            }

            return hasChanges;
        }

        /// <summary>
        /// Update customer credit card type
        /// </summary>
        /// <param name="customerCode">string customerCode</param>
        /// <param name="creditCardCode">string creditCardCode</param>
        /// <param name="cardType">string cardType</param>
        public static void UpdateCustomerCreditCardType(string customerCode, string creditCardCode, string cardType)
        {
            DB.ExecuteSQL("UPDATE CustomerCreditCard SET CreditCardType = {0} WHERE CustomerCode = {1} AND CreditCardCode = {2}",
                                    cardType.ToDbQuote(),
                                    customerCode.ToDbQuote(),
                                    creditCardCode.ToDbQuote());
        }

        /// <summary>
        /// Update Customer Business Type
        /// </summary>
        /// <param name="customerCode">string customerCode</param>
        /// <param name="businessType">InterpriseSuiteEcommerceCommon.Customer.BusinessTypes businessType</param>
        public static void UpdateCustomerBusinessType(string customerCode, InterpriseSuiteEcommerceCommon.Customer.BusinessTypes businessType)
        {
            DB.ExecuteSQL("UPDATE Customer SET BusinessType = {0} WHERE CustomerCode = {1}",
                                    businessType.ToString().ToDbQuote(),
                                    customerCode.ToDbQuote());
        }

        /// <summary>
        /// Update Customer Tax Number
        /// </summary>
        /// <param name="customerCode">string customerCode</param>
        /// <param name="taxNumber">string taxNumber</param>
        public static void UpdateCustomerBusinessTaxNumber(string customerCode, string taxNumber)
        {
            DB.ExecuteSQL("UPDATE Customer SET TaxNumber = {0} WHERE CustomerCode = {1}",
                                    taxNumber.ToDbQuote(),
                                    customerCode.ToDbQuote());
        }

        /// <summary>
        /// Clears CustomerShipTo / CustomerCreditCard Plus4 field value (Only applicable for US postal code)
        /// </summary>
        /// <param name="customerCode">string code</param>
        /// <param name="taxNumber">AddressTypes addressType</param>
        public static void ClearCustomerAddressPlus4Field(string code, AddressTypes addressType)
        {

            if (addressType == AddressTypes.Shipping)
            {
                DB.ExecuteSQL("UPDATE CustomerShipTo SET Plus4 = NULL WHERE ShipToCode = {0}", code.ToDbQuote());

            }else{

                DB.ExecuteSQL("UPDATE CustomerCreditCard SET Plus4 = NULL WHERE CreditCardCode = {0}", code.ToDbQuote());
            }

        }

        /// <summary>
        /// Update CustomerCreditCard Plus4 field (Only applicable for US postal code)
        /// </summary>
        /// <param name="customerCode">string code</param>
        /// <param name="taxNumber">int value</param>
        public static void UpdateCustomerCreditCardPlus4Field(string code, int value)
        {
            if (value > 0)
            {
                DB.ExecuteSQL("UPDATE CustomerCreditCard SET Plus4 = {1} WHERE CreditCardCode = {0}", code.ToDbQuote(), value.ToString());
            }

        }

        public static IEnumerable<CustomerInfo> GetCustomers()
        {
            string query = @"SELECT Address, City, State, PostalCode, County, Country, CustomerCode, FirstName, LastName, CustomerName, DateCreated 
                             FROM Customer";

            var customers = new List<CustomerInfo>();
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRS(query, con))
                {
                    while (reader.Read())
                    {
                        var address = new List<AddressDTO>();
                        address.Add(new AddressDTO()
                        {
                            address = DB.RSField(reader, "Address"),
                            city = DB.RSField(reader, "City"),
                            state = DB.RSField(reader, "State"),
                            postalCode = DB.RSField(reader, "PostalCode"),
                            county = DB.RSField(reader, "County"),
                            country = DB.RSField(reader, "Country")
                        });

                        var customer = new CustomerInfo()
                        {
                            CustomerCode = DB.RSField(reader, "CustomerCode"),
                            FirstName = DB.RSField(reader, "FirstName"),
                            LastName = DB.RSField(reader, "LastName"),
                            FullName = DB.RSField(reader, "CustomerName"),
                            DateRegistered = DB.RSFieldDateTime(reader, "DateCreated"),
                            Address = address
                        };
                        customers.Add(customer);
                    }
                }
            }
            return customers.AsEnumerable();
        }

        public static IEnumerable<CustomerSalesOrder> GetWebSalesOrders()
        {
            string query = @"SELECT SalesOrderCode, SalesOrderDate, TotalRate, BillToCode, BillToName, CurrencyCode 
                             FROM CustomerSalesOrder 
                             WHERE WebsiteCode={0} AND SourceCode='Internet' AND Type='Sales Order'".FormatWith(InterpriseHelper.ConfigInstance.WebSiteCode.ToDbQuote());
            
            var orders = new List<CustomerSalesOrder>();
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRS(query, con))
                {
                    while (reader.Read())
                    {
                        var order = new CustomerSalesOrder()
                        {
                            SalesOrderCode = DB.RSField(reader, "SalesOrderCode"),
                            SalesOrderDate = DB.RSFieldDateTime(reader, "SalesOrderDate"),
                            Total = DB.RSFieldDecimal(reader, "TotalRate"),
                            CustomerCode = DB.RSField(reader, "BillToCode"),
                            CustomerName = DB.RSField(reader, "BillToName"),
                            CurrencyCode = DB.RSField(reader, "CurrencyCode")
                        };
                        orders.Add(order);
                    }
                }
            }
            return orders.AsEnumerable();
        }

        public static IEnumerable<CustomerInvoice> GetWebInvoice()
        {
            string query = @"SELECT InvoiceCode, InvoiceDate, Total 
                             FROM CustomerInvoice
                             WHERE WebsiteCode={0} AND SourceCode='Internet' AND Type='Invoice'".FormatWith(InterpriseHelper.ConfigInstance.WebSiteCode.ToDbQuote());
            
            var invoices = new List<CustomerInvoice>();
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRS(query, con))
                {
                    while (reader.Read())
                    {
                        var invoice = new CustomerInvoice()
                        {
                            InvoiceCode = DB.RSField(reader, "InvoiceCode"),
                            InvoiceDate= DB.RSFieldDateTime(reader, "InvoiceDate"),
                            Total = DB.RSFieldDecimal(reader, "Total"),
                        };
                        invoices.Add(invoice);
                    }
                }
            }
            return invoices.AsEnumerable();
        }

        public static CustomerInfo GetCustomerInfoByRegistryID(Guid? giftRegistryID)
        {
            CustomerInfo customerInfo = null;

            if (!giftRegistryID.HasValue) return customerInfo;

            string query = @"SELECT cc.ContactCode, EntityCode as CustomerCode FROM crmContact cc
	                            INNER JOIN ecommerceGiftRegistry egr
	                            on cc.ContactGuid = egr.ContactGuid
	                            WHERE egr.RegistryID = {0}".FormatWith(giftRegistryID.Value.ToString().ToDbQuote());

            
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRS(query, con))
                {
                    if(reader.Read())
                    {
                        customerInfo = new CustomerInfo()
                        {
                            ContactCode = DB.RSField(reader, "ContactCode"),
                            CustomerCode = DB.RSField(reader, "CustomerCode")
                        };
                    }
                }
            }
            return customerInfo;
        }

    }
}
